package com.ctibook.booksystem.dao.bookcatalog.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.cti.ftpservice.dao.impl.BaseDao;
import com.cti.ftpservice.ftp.DateHelper;
import com.cti.ftpservice.vo.PageViewVO;
import com.cti.ftpservice.vo.ParameterVo;
import com.ctibook.booksystem.dao.bookcatalog.BookInfoDao;
import com.ctibook.booksystem.model.bookcatalog.BookInfo;
import com.ctibook.booksystem.model.bookcatalog.BookInfoExtral;
import com.ctibook.booksystem.vo.bookcatalog.BookInfoVO;
import com.ctibook.booksystem.vo.bookcatalog.BookInfoViewVO;

/**
 * @author 鲁永刚 E-mail: luyg@ctibook.com
 * @version 创建时间：Sep 10, 2014 11:03:38 AM 书目数据访问实现类
 */
@Repository("bookInfoDao")
public class BookInfoDaoImpl extends BaseDao implements BookInfoDao {

	@Override
	public void addOrUpdateBookInfo(BookInfo bookInfo) {
		addOrUpdateObj(bookInfo);
	}

	@Override
	public void deleteBookInfo(BookInfo bookInfo) {
		deleteObj(bookInfo);
	}

	@Override
	public BookInfo getBookInfoById(String id) {
		return getModel(BookInfo.class, id);
	}
	
	@Override
	public String getBookInfoTitleByIsbn(String isbn) {
		String hql = "select t.title from " + BookInfo.class.getName()
				+ " t where t.isbn = :isbn";
		Map<String, Object> mapParams = new HashMap<String, Object>();
		mapParams.put("isbn", isbn);
		return getSingleObject(hql, mapParams);
	}
	
	@Override
	public String getBookInfoPublisherNameByIsbn(String isbn) {
		String hql = "select t.publisher.fullName from " + BookInfo.class.getName()
				+ " t where t.isbn = :isbn";
		Map<String, Object> mapParams = new HashMap<String, Object>();
		mapParams.put("isbn", isbn);
		return getSingleObject(hql, mapParams);
	}
	
	@Override
	public String getPublisherIdByBookInfoId(String bookInfoId) {
		String sql = "select publisher_id from sysbook2017.t_bookcatalog_bookinfo t where t.id = :id";
		Map<String, Object> mapParams = new HashMap<String, Object>();
		mapParams.put("id", bookInfoId);
		return getSingleObjcetForSql(sql,mapParams);
	}
	
	

	@Override
	public BookInfo getBookInfoByIsbn(String isbn) {
		String hql = "from " + BookInfo.class.getName()
				+ " t where t.isbn = :isbn and t.checkStatus=:checkStatus";
		Map<String, Object> mapParams = new HashMap<String, Object>();
		mapParams.put("isbn", isbn);
		return getSingleObject(hql, mapParams);
	}
	
	@Override
	public int getBookInfoExistByIsbn(String isbn) {
		String hql = "select count(t.isbn) from " + BookInfo.class.getName()
				+ " t where t.isbn = :isbn";
		Map<String, Object> mapParams = new HashMap<String, Object>();
		mapParams.put("isbn", isbn);
		long count = getSingleObject(hql, mapParams);
		return (int) count;
	}
	
	
	
	@Override
	public BookInfo getBookInfoByIsbnAndStatus(String isbn, String status) {
		String hql = "from " + BookInfo.class.getName()
				+ " t where t.isbn = :isbn ";
		Map<String, Object> mapParams = new HashMap<String, Object>();
		mapParams.put("isbn", isbn);
		if(!StringUtils.isBlank(status)){
			hql+=" and t.checkStatus=:checkStatus";
			mapParams.put("checkStatus", status);	
		}
		return getSingleObject(hql, mapParams);
	}

	@Override
	public int getCountByPublisherId(String publisherId) {
		String hql = "select count(*) from " + BookInfo.class.getName()
				+ " t where t.publisher.id = :publisherId";
		Map<String, Object> mapParams = new HashMap<String, Object>();
		mapParams.put("publisherId", publisherId);
		long count = getSingleObject(hql, mapParams);
		return (int) count;
	}

	@Override
	public int getCountBySubjectId(String subjectId) {
		String hql = "select count(t) from "
				+ BookInfo.class.getName()
				+ " t inner join t.subjects subject where subject.id=:subjectId";
		Map<String, Object> mapParams = new HashMap<String, Object>();
		mapParams.put("subjectId", subjectId);
		long count = getSingleObject(hql, mapParams);
		return (int) count;
	}

	@Override
	public void addOrUpdateBookInfoVo(BookInfoVO vo) {
		addObj(vo);
	}

	@Override
	public PageViewVO<BookInfoVO> getBookInfoVoPageView(int pageIndex,
			int pageSize) {
		String hql = "from " + BookInfoVO.class.getName() + " t";
		return getPageViewVO(hql, pageIndex, pageSize);
	}

	@Override
	public BookInfoVO getBookInfoVoById(String id) {
		return getModel(BookInfoVO.class, id);
	}

	@Override
	public void deleteBookInfoVo(BookInfoVO vo) {
		deleteObj(vo);
	}

	@Override
	public List<String> getAllBlackIsbns() {
		String hql = "select isbn from "
				+ BookInfo.class.getName()
				+ " t where t.isCheckApprovedByHuman = :isCheckApprovedByHuman and t.isBlackBook=:isBlackBook";
		Map<String, Object> mapParams = new HashMap<String, Object>();
		mapParams.put("isCheckApprovedByHuman", true);
		mapParams.put("isBlackBook", true);
		return getListObject(hql, mapParams);
	}

	@Override
	public List<String> getAllWhiteIsbns() {
		String hql = "select isbn from "
				+ BookInfo.class.getName()
				+ " t where t.isCheckApprovedByHuman = :isCheckApprovedByHuman and t.isBlackBook=:isBlackBook";
		Map<String, Object> mapParams = new HashMap<String, Object>();
		mapParams.put("isCheckApprovedByHuman", true);
		mapParams.put("isBlackBook", false);
		return getListObject(hql, mapParams);
	}

	@Override
	public List<BookInfo> getBookInfosByIds(List<String> bookIds) {
		String hql = "from " + BookInfo.class.getName()
				+ " t where t.id in (:ids)";
		Map<String, Object> mapParams = new HashMap<String, Object>();
		mapParams.put("ids", bookIds);
		return getListObject(hql, mapParams);
	}
	
	@Override
	public PageViewVO<BookInfo> getBlackBookInfo(String[] isbns,int pageIndex,int pageSize) {
		String hql = " from " + BookInfo.class.getName() + " t where t.isBlackBook=:blackBook and t.isCheckApprovedByHuman=:humanCheck";
		Map<String, Object> mapParams = new HashMap<String, Object>();
		mapParams.put("blackBook", true);
		mapParams.put("humanCheck", true);
		if(isbns!=null && isbns.length>0){
			if(isbns.length==1){
				hql+=" and t.isbn=:isbn";
				mapParams.put("isbn", isbns[0]);
			}else{
				hql+=" and t.isbn in (:isbn)";
				mapParams.put("isbn", isbns);
			}
		}
		return getPageViewVO(hql,mapParams, pageIndex, pageSize);
	}

	@Override
	public PageViewVO<BookInfo> getBookInfoDetailsVoByCheckStatus(String[] checkStatus,String[] isbns,int pageIndex,int pageSize,String uploadUserId) {
		String hql = " from " + BookInfo.class.getName() + " t where 1=1 ";
		Map<String, Object> mapParams = new HashMap<String, Object>();
		if(checkStatus!=null && checkStatus.length>0){
			if(checkStatus.length==1){
				hql+= " and t.checkStatus=:checkStatus";
				mapParams.put("checkStatus", checkStatus[0]);
			}else{
				hql+= " and t.checkStatus in (:checkStatus)";
				mapParams.put("checkStatus", checkStatus);
			}
		}
		if(isbns!=null && isbns.length>0){
			if(isbns.length==1){
				hql+= " and t.isbn=:isbns";
				mapParams.put("isbns", isbns[0]);
			}else{
				hql+= " and t.isbn in (:isbns)";
				mapParams.put("isbns", isbns);
			}
		}
		if(!StringUtils.isBlank(uploadUserId)){
			hql+=" and t.createUser.id=:userId";
			mapParams.put("userId", uploadUserId);
		}
		hql+=" order by t.lastUpdateDate desc";
		return getPageViewVO(hql,mapParams, pageIndex, pageSize);	
	}
	
	
	public List<String> getbookinfoIsbnByCategoryIdsAndPublisherYear(String[] categoryIds,Integer[] publisherYears,ParameterVo[] parameterVos){
		String hql = "select distinct t.isbn from "
				+ BookInfo.class.getName()
				+ " t ";
		String joinHql=" join t.categories category ";
		String whereHql=" where 1=1 ";
		Map<String, Object> mapParams = new HashMap<String, Object>();
		if(parameterVos!=null&&parameterVos.length>0){
			for (ParameterVo parameterVo : parameterVos) {
				whereHql+=parameterVo.getParameterSqlProperty();
				mapParams.put(parameterVo.getParameterName(), parameterVo.getParameterValue());
			}
		}
		if(categoryIds!=null&&categoryIds.length>0){
			hql+=joinHql;
			if(categoryIds.length==1)
				whereHql+=" and category.id=:categoryIds ";
			else
				whereHql+=" and category.id in (:categoryIds) ";
			mapParams.put("categoryIds", categoryIds);
		}
		if(publisherYears!=null&&publisherYears.length>0){
			if(publisherYears.length==1)
				whereHql+=" and t.pubYear=:pubYear ";
			else
				whereHql+=" and t.pubYear in (:pubYear) ";
			mapParams.put("pubYear", publisherYears);
		}
		return getListObject(hql+whereHql, mapParams);
	}

	@Override
	public List<BookInfo> getbookInfosByMultiplyQuery(ParameterVo[] parameterVos) {
		String hql=" from "+BookInfo.class.getName()+" t where 1=1 ";
		if(parameterVos==null||parameterVos.length==0)
			return getListObject(hql);
		Map<String, Object> mapParams=new HashMap<String, Object>();
		for (ParameterVo parameterVo : parameterVos) {
			hql+=parameterVo.getParameterSqlProperty();
			if(parameterVo.getParameterValue()!=null){
				if(parameterVo.getParameterType()!=null&&parameterVo.getParameterType().equals("java.Util.Date")){
					java.util.Date paraDate=DateHelper.stringToDate(parameterVo.getParameterValue().toString(), "yyyy-MM-dd");
					mapParams.put(parameterVo.getParameterName(),paraDate);
				}else{
					mapParams.put(parameterVo.getParameterName(), parameterVo.getParameterValue());
				}
			}
			
		}
		return getListObject(hql, mapParams);
	}
	
	@Override
	public List<String> getIsbnByParameterVo(ParameterVo[] parameterVos){
		String sql="select t.isbn from "+BookInfo.class.getName()+" t where 1=1 ";
		Map<String, Object> mapParams=new HashMap<String, Object>();
		if(parameterVos!=null && parameterVos.length>0){
			for(ParameterVo parameterVo : parameterVos){
				sql+=parameterVo.getParameterSqlProperty();
				if(parameterVo.getParameterValue()!=null)
					mapParams.put(parameterVo.getParameterName(), parameterVo.getParameterValue());	
			}
		}
	   return getListObject(sql, mapParams);
	} 
	
	@Override
	public List<String> getIsbnsByCategoryIdsAndIsbns(String[] categoryIds,String isbns[]){
		String sql=" select distinct a.isbn from sysbook2017.t_bookcatalog_bookinfo a,sysbook2017.t_bookcatalog_bookinfo_category b "
                   + "where a.id=b.bookinfo_id";
		Map<String, Object> mapParams=new HashMap<String, Object>();
		if(isbns!=null && isbns.length>0){
			if(isbns.length==1){
				sql+=" and a.isbn=:isbn";
				mapParams.put("isbn", isbns[0]);
			}else if(isbns.length>1){
				sql+=" and a.isbn in (:isbn)";
				mapParams.put("isbn", isbns);
			}
		}
		if(categoryIds!=null && categoryIds.length>0){
			if(categoryIds.length==1){
				sql+=" and b.category_id=:categoryId";
				mapParams.put("categoryId", categoryIds[0]);
			}else if(categoryIds.length>1){
				sql+=" and b.category_id in (:categoryId)";
				mapParams.put("categoryId", categoryIds);
			}
		}
		return getListObjcetForSql(sql, mapParams);
	}
	
	
	
	private String getBookInfoViewVOForSql(){
		return "select t.id,t.isbn,t.title,t.chineseTitle,t.author,t.binding,t.`language`,t.currency,t.price,t.isCheckApprovedByHuman,t.humanCheckApproveResult,"+
               "t.pubYear,t.isBlackBook,a.id as publisherId,a.alisName as pubAlisName,a.fullName as pubFullName from "+
               "t_bookcatalog_bookinfo t LEFT JOIN t_importdepartment_publisher a on t.publisher_id=a.id ";
	}

	@Override
	public BookInfoViewVO getBookInfoViewVOById(String id) {
		String sql=getBookInfoViewVOForSql();
		sql+="where t.id=:id";
		Map<String, Object> mapParams = new HashMap<String, Object>();
		mapParams.put("id", id);
		List<BookInfoViewVO> vos=getTransFormListObjcetForSql(BookInfoViewVO.class, sql, mapParams, 
				new String[]{"pubDate"});
		if (vos.size() > 0)
			return vos.get(0);
		return null;
	}

	@Override
	public BookInfoViewVO getBookInfoViewVOByIsbn(String isbn) {
		String sql=getBookInfoViewVOForSql();
		sql+="where t.isbn=:isbn";
		Map<String, Object> mapParams = new HashMap<String, Object>();
		mapParams.put("isbn", isbn);
		List<BookInfoViewVO> vos=getTransFormListObjcetForSql(BookInfoViewVO.class, sql, mapParams, 
				new String[]{"pubDate"});
		if (vos!=null&&vos.size() > 0)
			return vos.get(0);
		return null;
	}

	@Override
	public String getBookCategoryByBookInfoId(String bookInfoId, Integer level) {
		String sql="select GROUP_CONCAT( DISTINCT b.`name` separator ';') from sysbook2017.t_bookcatalog_bookinfo_category a"+
               " LEFT JOIN sysbook2017.t_bookcatalog_bookcategory b on a.category_id=b.id"+
               " where a.bookinfo_id=:bookInfoId";
		Map<String, Object> mapParams = new HashMap<String, Object>();
		mapParams.put("bookInfoId", bookInfoId);
		if(level!=null){
			sql+=" and b.`level`=:level";
			mapParams.put("level", level);
		}
		return getSingleObjcetForSql(sql,mapParams);
	}

	
	@Override
	public void updateBookInfoForSql(String exSql) {
		executeUpdateSql(exSql);
	}

	@Override
	public PageViewVO<BookInfo> getTopUnUpdatePriceBookInfoItems(int pageSize) {
		String hql=" from "+BookInfo.class.getName()+" t where t.updatePrice=0 ";
		return getPageViewVO(hql, 1,pageSize);
	}

	@Override
	public BookInfoExtral getBookInfoExtralByISBN(String ISBN) {
		String hql=" from " +BookInfoExtral.class.getName()+" t where t.isbn=:isbn ";
		Map<String, Object> mapParams = new HashMap<String, Object>();
		mapParams.put("isbn", ISBN);
		return getSingleObject(hql, mapParams);
	}

	public BookInfoExtral getBookInfoExtralById(String id){
		return getModel(BookInfoExtral.class, id);
	}

	@Override
	public void addOrUpdateBookInfoExtral(BookInfoExtral bookInfoExtral) {
		addOrUpdateObj(bookInfoExtral);
	}

	@Override
	public List<String> getNotImagesIsbns(int pageIndex, int pageSize) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<String> getBookImagePaths(String isbn) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Map<String, String> getNotThumbsImageIsbnAndFirstImages(int pageIndex, int pageSize) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Map<String, String> getThumbsImagePathByIsbns(List<String> isbns) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<BookInfo> getBooksBySubscripeUserInfo(List<String> subjectIds, List<String> categoryIds,
			String subscripeUserId, int minBooksCount) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<BookInfo> getBookInfoVosBySubjectIdsAndNotInSubjectBookListRecord(List<String> subjectIds,
			int maxResultSize) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<BookInfo> getBookInfoVosByCategoryIdsAndNotInCategoryBookListRecord(List<String> categoryIds,
			int maxResultSize) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<BookInfo> getBookInfoVosByNotInMonthBookListRecord(int maxResultSize) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void deleteBookInfoExtral(BookInfoExtral bookInfoExtral) {
		deleteObj(bookInfoExtral);
	}

}
